/*You may amend and distribute as you like, but don't remove this header!
 * 
 * This lets users query databases from the ScrewTurn Wiki.
 * 
 * Copyright 2008 Scott Clewell, All Rights Reserved.
 * 
 * This is an Open Source project provided under the 
 * GNU General Public License (GPL) as published by the 
 * Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
 * 
 * The GNU General Public License can be viewed at http://www.opensource.org/licenses/gpl-license.php
 * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
 * 
 * The code for this project may be used and redistributed by any means providing that this notice 
 * and the author's name and all copyright notices remain intact.
 * 
 * All code and executables are provided "as is" with no warranty either express or implied. 
 * The author accepts no liability for any damage or loss of business that this product may cause.
*/

using System;
using System.Collections.Generic;
using System.Text;
using ScrewTurn.Wiki.PluginFramework;
using System.Data.SqlClient;
using System.Data;
using System.Text.RegularExpressions;

namespace WikiSQLTableFormatter
{
    public class SQLTableFormatter : SQLTableFormatterBase, IFormatterProvider
    {
        //Version Information
        private const string CurrentVersion = "1.0";
        private const string CurrentRevision = ".0";

        //Tags that the query are contained in (Default is phase1)
        private static Regex QueryTableTag = new Regex(@"\{QueryTable(.*?)\}(.+?)\{\/QueryTable\}", RegexOptions.Compiled | RegexOptions.IgnoreCase | RegexOptions.Singleline);

        //Sets up the information for this provider
        private ComponentInformation info = new ComponentInformation("SQL Table Formatter " + CurrentVersion + CurrentRevision, "CommunityAmerica Credit Union", "http://www.CACU.com");
        public ComponentInformation Information
        {
            get { return info; }
        }

        //Interface Implementions
        public bool PerformPhase1
        {
            get { return true; }
        }
        public bool PerformPhase2
        {
            get { return false; }
        }
        public virtual bool PerformPhase3
        {
            get { return false; }
        }

        protected override bool ValidateConfig()
        { 
            //Perform a command to make sure connection string for the SQL server is valid.
            SqlCommand command = GetCommand();
            command.CommandText = "Select 1 as one";

            if (ExecuteNonQuery(command))
                return true;
            else
                return false;
        }

        public string Format(string raw, ContextInformation context, FormattingPhase phase)
        {
            string results = "";
            try
            {
                //Assings regex tags according to phase
                Match match;
                //if (phase == FormattingPhase.Phase1)
                match = QueryTableTag.Match(raw);
                //else if (phase == FormattingPhase.Phase2)
                //    match = QueryTableTagPhase2.Match(raw);
                //else
                //    match = QueryTableTag.Match(raw);

                if (!match.Success)
                    results = raw;
                else
                {
                    //At least one tag was found
                    int start = 0;
                    while (match.Success)
                    {
                        //May be more than one match, loop through and process
                        try
                        {
                            //Groups[1] contains parameters, Groups[2] contains query
                            string query = match.Groups[2].Value;
                            string parameters = match.Groups[1].Value;

                            //Perserves previous data
                            if ((match.Index - start) > 0)
                                results += raw.Substring(start, match.Index - start);

                            //Checks if query has a query in it.
                            if (query.Trim() == "")
                            {
                                //No query was in tags
                                results += @"QueryTable: <font color=red>Error#1: No query was found in tags</font>";
                            }
                            else
                            {
                                //query was found, run against server
                                SqlCommand command = GetCommand();
                                command.CommandText = query;
                                //Execute query and get results
                                DataSet ds = ExecuteQuery(command);
                                bool AddBorderWidth;
                                string TableClass = "QT" + match.Index.ToString();
                                if (ds != null)
                                {
                                    if (parameters != "")
                                    {
                                        //Sends the parameters to be added to a css tag if there are any
                                        results += FormatParameters(parameters,TableClass);
                                        AddBorderWidth = false;
                                    }
                                    else
                                    {
                                        AddBorderWidth = true;
                                    }
                                    //Formats the dataset into a html tagged table
                                    if (parameters.ToLower().Contains("noheaders"))
                                        results += FormatDataSet(ds, TableClass, AddBorderWidth, false);
                                    else
                                        results += FormatDataSet(ds, TableClass, AddBorderWidth);
                                }
                                else
                                    results += @"QueryTable: <font color=red>Error#2: There was an error in the query.</font>";
                            }
                            //updates match to after the occurance
                            start = match.Index + match.Length;
                            match = match.NextMatch();
                        }
                        catch(Exception)
                        {
                            //Return an error for the table 
                            //TODO: Change e.Message on release version.
                            results += "QueryTable:<font color=red> Error#3: Invalid Query: They query returned with errors. Check object names and syntax.</font>";

                            //updates the match index.
                            start = match.Index + match.Length;
                            match = match.NextMatch();
                        }
                    }
                    //Adds rest of page
                    if (start < raw.Length)
                        results += raw.Substring(start);
                }
            }
            catch(Exception e)
            {
                //TODO: Change e.Message on release version
                host.LogEntry(e.Message + ", " + e.StackTrace, LogEntryType.Error, this);
                return results += "QueryTable:<font color=red> Error#4: QueryTable returned with errors, The errors have been logged. Please contact the administrator.</font>";
            }
            return results;
        }

        /// <summary>
        /// Turns a dataset into an html tagged table.
        /// </summary>
        /// <param name="ds">DataSet to be turned into the table.</param>
        /// <param name="TableClass"></param>
        /// <param name="AddBorderWidth"></param>
        /// <returns>String of html tag table.</returns>
        public string FormatDataSet(DataSet ds,string TableClass,bool AddBorderWidth)
        {
            //goes through dataset and creates a html table based off of the data.
            string formatted = "";
            if(!AddBorderWidth)
                formatted = "<table class=\"" + TableClass + "\"><tr>";
            else
                formatted = "<table border=1><tr>";

            //Gets column names
            for (int x = 0; x < ds.Tables[0].Columns.Count; x++)
            {
                formatted += "<th>" + ds.Tables[0].Columns[x].ColumnName + "</th>";
            }
            formatted += "</tr>";

            //Gets data 
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                formatted += "<tr>";
                foreach (DataColumn col in ds.Tables[0].Columns)
                {
                    //If it contains http:// then make it a link
                    if(row[col.ColumnName].ToString().ToLower().Contains("http://"))
                        formatted += "<td><a href=\"" + row[col.ColumnName].ToString() + "\">" + row[col.ColumnName].ToString() + "</a></td>";
                    else 
                        formatted += "<td>" + row[col.ColumnName].ToString() + "</td>";
                }
                formatted += "</tr>";
            }
            formatted += "</table>";
            return formatted;
        }

        /// <summary>
        /// Turns a dataset into an html tagged table.
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="TableClass"></param>
        /// <param name="AddBorderWidth"></param>
        /// <param name="IncludeHeaders"></param>
        /// <returns></returns>
        public string FormatDataSet(DataSet ds, string TableClass, bool AddBorderWidth, bool IncludeHeaders)
        {
            //goes through dataset and creates a html table based off of the data.
            string formatted = "";
            if (!AddBorderWidth)
                formatted = "<table class=\"" + TableClass + "\"><tr>";
            else
                formatted = "<table border=1><tr>";

            //Gets column names
            if (IncludeHeaders)
            {
                for (int x = 0; x < ds.Tables[0].Columns.Count; x++)
                {
                    formatted += "<th>" + ds.Tables[0].Columns[x].ColumnName + "</th>";
                }
                formatted += "</tr>";
            }

            //Gets data 
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                formatted += "<tr>";
                foreach (DataColumn col in ds.Tables[0].Columns)
                {
                    formatted += "<td>" + row[col.ColumnName].ToString() + "</td>";
                }
                formatted += "</tr>";
            }
            formatted += "</table>";
            return formatted;
        }

        /// <summary>
        /// Formats a string that contains parameters for the QueryTable.
        /// </summary>
        /// <param name="Parameters">The string to be parsed into the css tag.</param>
        /// <param name="TableClass">The class of the html table.</param>
        /// <returns>A css tag with the properties assigned.</returns>
        public string FormatParameters(string Parameters, string TableClass)
        {
            //Creates a list of Tableproperties, then matches properties and adds them to a css tag
            List<TableProperty> properties = new List<TableProperty>();

            Regex PropertyRegex = new Regex("(.+?)=\"(.+?)\"", RegexOptions.Singleline | RegexOptions.IgnoreCase);

            Match match;
            match = PropertyRegex.Match(Parameters);
            //Regexes thought the parameters and adds the matches to the list
            while (match.Success)
            {
                properties.Add(new TableProperty(match.Groups[1].Value, match.Groups[2].Value));
                match = match.NextMatch();
            }

            //Adds Properties into the proper css string
            string TableCss = "";
            string CellCss = "";
            string HeaderFontCss = "";
            string CellFontCss = "";
            bool HasBorderColor = false;
            bool HasBorderWidth = false;
            bool HasBorderStyle = false;
            bool HasCellBorderColor = false;
            bool HasCellBorderWidth = false;
            bool HasCellBorderStyle = false;
            

            foreach (TableProperty p in properties)
            {
                if ((p.Property.ToLower().Contains("cell")) && !p.Property.ToLower().Contains("font")) //Cells
                {
                    CellCss += p.Property.Replace("cell-","") + ": " + p.Value + ";";
                    if (p.Property.ToLower().Trim() == "cell-border-color")
                        HasCellBorderColor = true;
                    else if (p.Property.ToLower().Trim() == "cell-border-width")
                        HasCellBorderWidth = true;
                    else if (p.Property.ToLower().Trim() == "cell-border-style")
                        HasCellBorderStyle = true;
                }
                else if (!p.Property.ToLower().Contains("font") && !p.Property.ToLower().Contains("font")) //Table
                {
                    TableCss += p.Property + ": " + p.Value + ";";
                    if (p.Property.ToLower().Trim() == "border-color")
                        HasBorderColor = true;
                    else if (p.Property.ToLower().Trim() == "border-width")
                        HasBorderWidth = true;
                    else if (p.Property.ToLower().Trim() == "border-style")
                        HasBorderStyle = true;
                }
                else if (p.Property.ToLower().Contains("font")) //font (May need reworking in future
                {
                    if (p.Property.ToLower().Trim() == "header-font-color")
                        HeaderFontCss += " color: " + p.Value;
                    else if (p.Property.ToLower().Trim() == "cell-font-color")
                        CellFontCss += " color: " + p.Value;
                }
            }

            //Adds missing tags
            if ((HasBorderColor == true || HasBorderStyle == true) && HasBorderWidth == false)
                TableCss += "border-width: 1px;";
            if ((HasBorderColor == true || HasBorderWidth == true) && HasBorderStyle == false)
                TableCss += "border-style: solid;";

            if ((HasCellBorderColor == true || HasBorderStyle == true) && HasCellBorderWidth == false)
                CellCss += "border-width: 1px;";
            if ((HasCellBorderColor == true || HasCellBorderWidth == true) && HasCellBorderStyle == false)
                CellCss += "border-style: solid";

            //bulids css tag
            string css = "<style type=\"text/css\">table." + TableClass
                + "{ " + TableCss + " } "
                + "table." + TableClass
                + " th { " + CellCss + HeaderFontCss + "} "
                + "table." + TableClass
                + " td { " + CellCss + CellFontCss + "} "
                + "</style>\n";

            return css;
        }
    }
}
